﻿' Project : Quan Ly Khach Hang
' QuanLyKhachHang-DONASA®
' Copyright (c) 2010-2011
' Author : Trần Nguyên Lãm
' Created Date : 22/05/2011
' Added BindData Function  By LamTN
Imports System.Data.OleDb
Public Class frmThongKePhiVanChuyen
    Private ReportType As Integer = 0
    Private cm As New Common
    Private groupingList As DataTable
    Private aMaKhachHang As New AutoCompleteStringCollection
#Region "Form Event"
    Private Sub frmThongKeDoanhThu_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        DisableControl()
        dtpTheoNgay.Enabled = True
        Nam_Load()
        Thang_Load()
        Report_TheoNgay()
        AutoComplete_MaKhachHang()
    End Sub
    Function ConvertVietnamese(ByVal Ngay As String) As String
        Try
            If Ngay = "" Then
                Return ""
            Else
                Dim aNgay() As String = Ngay.Split("/")
                If Integer.Parse(aNgay(0)) <= 9 Then
                    aNgay(0) = "0" & aNgay(0)
                End If
                If Integer.Parse(aNgay(1)) <= 9 Then
                    aNgay(1) = "0" & aNgay(1)
                End If
                Return aNgay(0) & "/" & aNgay(1) & "/" & aNgay(2)
            End If
        Catch ex As Exception

        End Try
    End Function
    Sub DisableControl()
        dtpTheoNgay.Enabled = False
        cbxTheoThang.Enabled = False
        cbxNamThang.Enabled = False
        cbxTheoNam.Enabled = False
        dtpTuNgay.Enabled = False
        dtpDenNgay.Enabled = False
        txtTenKhachHang.Enabled = False
        Panel_KhachHang.Visible = False
        txtTenKhachHang.Text = ""
        txtMaKhachHang.Text = ""
        txtMaKhachHang.Enabled = False
        lblKhachHangId.Text = "0"
    End Sub

    Private Sub rbtTheoNgay_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTheoNgay.CheckedChanged
        DisableControl()
        dtpTheoNgay.Enabled = True
        dtpTheoNgay.Value = Now.Date
        ReportType = 0
    End Sub
    Private IsPostBack As Boolean = False
    Private Sub rbtTheoThang_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTheoThang.CheckedChanged
        DisableControl()
        cbxTheoThang.Enabled = True
        If IsPostBack = False Then
            cbxTheoThang.SelectedIndex = IndexCurrentMonth
        End If
        cbxNamThang.Enabled = True
        If IsPostBack = False Then
            cbxNamThang.SelectedIndex = 0
        End If
        ReportType = 1
        IsPostBack = True
    End Sub
    Private IsPostBack2 As Boolean = False
    Private Sub rbtTheoNam_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTheoNam.CheckedChanged
        DisableControl()
        cbxTheoNam.Enabled = True
        If IsPostBack2 = False Then
            cbxTheoNam.SelectedIndex = 0
        End If
        ReportType = 2
        IsPostBack2 = True
    End Sub

    Private Sub rbtTuNgay_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTuNgay.CheckedChanged
        DisableControl()
        dtpTuNgay.Enabled = True
        dtpTuNgay.Value = Now.Date
        dtpDenNgay.Enabled = True
        dtpDenNgay.Value = Now.Date
        ReportType = 3
    End Sub
    Private Sub rbtTenKhachHang_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTenKhachHang.CheckedChanged
        DisableControl()
        txtTenKhachHang.Enabled = True
        txtMaKhachHang.Enabled = True
        ReportType = 4
    End Sub
    Private Sub cmdThongKe_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdThongKe.Click
        'clear grid
        dgvThongKeDoanhThu.Rows.Clear()
        Select Case ReportType
            Case 0
                Report_TheoNgay()
            Case 1
                Report_TheoThang()
            Case 2
                Report_TheoNam()
            Case 3
                Report_TuNgayDenNgay()
            Case 4
                Report_TheoTenKhachHang()
        End Select
    End Sub

    'Function ConvertDateToInt(ByVal Ngay As String)
    '    Dim aNgay As String() = Ngay.Split("/")
    '    'Dim iThang As String
    '    'Dim iNgay As String
    '    Dim Ngay_So As String
    '    'If Integer.Parse(aNgay(1)) <= 9 Then
    '    '    iNgay = "0" & aNgay(1)
    '    'Else
    '    '    iNgay = aNgay(1)
    '    'End If
    '    'If Integer.Parse(aNgay(0)) <= 9 Then
    '    '    iThang = "0" & aNgay(0)
    '    'Else
    '    '    iThang = aNgay(0)
    '    'End If
    '    Ngay_So = aNgay(2) & aNgay(0) & aNgay(1)
    '    Try
    '        Return Integer.Parse(Ngay_So)
    '    Catch ex As Exception
    '        Return 0
    '    End Try
    'End Function

    Function formatDate_VN(ByRef Ngay As String) As Boolean
        Dim aNgay As String() = Ngay.Split("/")
        Ngay = aNgay(1) & "/" & aNgay(0) & "/" & aNgay(2)
    End Function
    Function TinhTongCong(ByVal dtTable As DataTable) As String
        Dim SoTien As Integer = 0
        For i As Integer = 0 To dtTable.Rows.Count - 1
            SoTien = SoTien + ConvertCurencyToInt(dtTable.Rows(i)("ThanhTien"))
        Next
        Return CDec(SoTien.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
    End Function
    Function ConvertCurencyToInt(ByVal Curency As String) As Integer
        Dim retInt As Integer
        If Curency = "" Then
            Return 0
        Else
            Curency = Curency.Split(",")(0).Replace(".", "")
            retInt = Integer.Parse(Curency)
            Return retInt
        End If
    End Function
    Protected Function CreateDataTableTong() As DataTable
        Dim dtTable As DataTable = New DataTable()
        Try
            Dim Tong As DataColumn = New DataColumn("TongCong")
            Tong.DataType = System.Type.GetType("System.String")
            dtTable.Columns.Add(Tong)
            Return dtTable
        Catch ex As Exception
            Throw
        End Try
    End Function
#End Region
#Region "Thống kê - báo cáo"
    '--------Tính tiền doanh thu của khách hàng trên 1 đơn hàng-------------------
    Private Function TinhDoanhThuKhachHangTheoDonHang(ByVal IdDonHangBan As Integer) As Single
        Dim sRet As Single = 0
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dtThanhTien As New DataTable
            Dim query_Tien_HoaDon As String
            Dim adapter As OleDb.OleDbDataAdapter
            Dim TongTienHoaDon As Integer = 0
            query_Tien_HoaDon = String.Format("Select ThanhTien From ChiTietDonHangBan Where LoaiPhi='HangHoa' And IdDonHangBan={0} ", IdDonHangBan)
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query_Tien_HoaDon, dbConn)
            adapter.Fill(dtThanhTien)
            For i As Integer = 0 To dtThanhTien.Rows.Count - 1
                sRet = sRet + dtThanhTien.Rows(i)("ThanhTien")
            Next
            Return sRet
        Catch ex As Exception
            MessageBox.Show("Có lỗi trong quá trình xử lý.Vui lòng thử lại")
        End Try
    End Function
    '--------Tính tiền doanh thu của khách hàng trên 1 đơn hàng-------------------
    Private Function TinhDoanhThuCongTyTheoDonHang(ByVal IdDonHangBan As Integer) As Single
        Dim sRet As Single = 0
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dtThanhTien As New DataTable
            Dim query_Tien_HoaDon As String
            Dim adapter As OleDb.OleDbDataAdapter
            Dim TongTienHoaDon As Integer = 0
            query_Tien_HoaDon = String.Format("Select ThanhTien From ChiTietDonHangBan Where LoaiPhi=N'GiamTru' And IdDonHangBan={0} ", IdDonHangBan)
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query_Tien_HoaDon, dbConn)
            adapter.Fill(dtThanhTien)
            For i As Integer = 0 To dtThanhTien.Rows.Count - 1
                sRet = sRet + dtThanhTien.Rows(i)("ThanhTien")
            Next
            Return sRet
        Catch ex As Exception
            MessageBox.Show("Có lỗi trong quá trình xử lý.Vui lòng thử lại")
        End Try
    End Function


    Private Function TinhTongDoanhThuKhachHang(ByVal KhachHangId As Integer, ByVal CalOption As Integer, Optional ByVal sSQL As String = "")
        Dim sRet As Single = 0
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dtThanhTien As New DataTable
            Dim query_Tien_HoaDon As String
            Dim adapter As OleDb.OleDbDataAdapter
            Dim TongTienHoaDon As Integer = 0
            query_Tien_HoaDon = String.Format("Select ThanhTien From ChiTietDonHangBan Where TrangThai=N'Đã duyệt' And LoaiPhi='HangHoa' And KhachHangId={0} ", KhachHangId)
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query_Tien_HoaDon, dbConn)
            adapter.Fill(dtThanhTien)
            For i As Integer = 0 To dtThanhTien.Rows.Count - 1
                sRet = sRet + dtThanhTien.Rows(i)("ThanhTien")
            Next
            'Tinh Thêm Tiền Làm Đại Lý
            dtThanhTien = New DataTable
            query_Tien_HoaDon = String.Format("Select ThanhTien From ChiTietDonHangBan Where TrangThai=N'Đã duyệt' And LoaiPhi='HangHoa' " & sSQL, KhachHangId)
            adapter = New OleDb.OleDbDataAdapter(query_Tien_HoaDon, dbConn)
            adapter.Fill(dtThanhTien)
            For i As Integer = 0 To dtThanhTien.Rows.Count - 1
                sRet = sRet + dtThanhTien.Rows(i)("ThanhTien")
            Next
            dbConn.Close()
            Return sRet
        Catch ex As Exception
            Throw
        End Try
    End Function
    Private Function TinhTongDoanhThuCongTy(ByVal KhachHangId As Integer, ByVal CalOption As Integer)
        Dim sRet As Single = 0
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dtThanhTien As New DataTable
            Dim query_Tien_HoaDon As String
            Dim adapter As OleDb.OleDbDataAdapter
            Dim TongTienHoaDon As Integer = 0
            query_Tien_HoaDon = String.Format("Select ThanhTien From ChiTietDonHangBan Where TrangThai=N'Đã duyệt' And LoaiPhi='GiamTru' And KhachHangId={0} ", KhachHangId)
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query_Tien_HoaDon, dbConn)
            adapter.Fill(dtThanhTien)
            For i As Integer = 0 To dtThanhTien.Rows.Count - 1
                sRet = sRet + dtThanhTien.Rows(i)("ThanhTien")
            Next
            'Tinh Thêm Tiền Làm Đại Lý
            dtThanhTien = New DataTable
            query_Tien_HoaDon = String.Format("Select ThanhTien From ChiTietDonHangBan Where TrangThai=N'Đã duyệt' And LoaiPhi='GiamTru' And IDDonHangBan In (Select IDDonHangBan From DonHangBan Where HinhThucTiepThi=N'Đại lý' And TrangThai=N'Đã duyệt' And NhanVienTiepThiId={0} And NgayBan='2/19/2011')", KhachHangId)
            adapter = New OleDb.OleDbDataAdapter(query_Tien_HoaDon, dbConn)
            adapter.Fill(dtThanhTien)
            For i As Integer = 0 To dtThanhTien.Rows.Count - 1
                sRet = sRet + dtThanhTien.Rows(i)("ThanhTien")
            Next
            dbConn.Close()
            Return sRet
        Catch ex As Exception
            Throw
        End Try
    End Function
#End Region
#Region "Thống kê theo ngày"
    Protected Function Create_GroupingList() As DataTable
        Dim dtTable As DataTable = New DataTable()
        Try

            Dim TenKhachHang As DataColumn = New DataColumn("TenKhachHang")
            TenKhachHang.DataType = System.Type.GetType("System.String")
            TenKhachHang.DefaultValue = ""
            dtTable.Columns.Add(TenKhachHang)

            Dim MaSoThue As DataColumn = New DataColumn("MaSoThue")
            MaSoThue.DataType = System.Type.GetType("System.String")
            MaSoThue.DefaultValue = ""
            dtTable.Columns.Add(MaSoThue)

            Dim DiaChi As DataColumn = New DataColumn("DiaChi")
            DiaChi.DataType = System.Type.GetType("System.String")
            DiaChi.DefaultValue = ""
            dtTable.Columns.Add(DiaChi)

            Dim NgayBan As DataColumn = New DataColumn("NgayBan")
            NgayBan.DataType = System.Type.GetType("System.String")
            dtTable.Columns.Add(NgayBan)

            Dim So As DataColumn = New DataColumn("So")
            So.DataType = System.Type.GetType("System.String")
            dtTable.Columns.Add(So)

            Dim PhiVanChuyen As DataColumn = New DataColumn("PhiVanChuyen")
            PhiVanChuyen.DataType = System.Type.GetType("System.String")
            PhiVanChuyen.DefaultValue = ""
            dtTable.Columns.Add(PhiVanChuyen)

            Dim Loai As DataColumn = New DataColumn("Loai")
            Loai.DataType = System.Type.GetType("System.String")
            Loai.DefaultValue = ""
            dtTable.Columns.Add(Loai)

            'Dim DoanhThuCTy As DataColumn = New DataColumn("DoanhThuCTy")
            'DoanhThuCTy.DataType = System.Type.GetType("System.String")
            'DoanhThuCTy.DefaultValue = ""
            'dtTable.Columns.Add(DoanhThuCTy)

            'Dim NguonDoanhThu As DataColumn = New DataColumn("NguonDoanhThu")
            'NguonDoanhThu.DataType = System.Type.GetType("System.String")
            'NguonDoanhThu.DefaultValue = ""
            'dtTable.Columns.Add(NguonDoanhThu)


            Return dtTable
        Catch ex As Exception
            Throw
        End Try
    End Function
    Sub Report_TheoNgay()
        Dim dbConn As New OleDb.OleDbConnection
        Dim dataAccess As New DataAccess
        Dim adapter As OleDb.OleDbDataAdapter
        dataAccess.ConnectDatabase_DBAccess(dbConn)
        Dim query As String = ""
        REM Grouping list
        groupingList = Create_GroupingList()
        'title
        Dim dtTenKhachHang As New DataTable
        If rbtTatCa.Checked Then
            query = "Select Distinct TenKhachHang,KhachHangId From DonHangBan Where Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai<>N'Đã hủy' And NgayBan='" & dtpTheoNgay.Value.ToShortDateString & "'"
            query = query & " Union Select Distinct NhanVienTiepThi As TenKhachHang,NhanVienTiepThiID As KhachHangId From DonHangBan Where NhanVienTiepThiId<>0 And Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai<>N'Đã hủy' And NgayBan='" & dtpTheoNgay.Value.ToShortDateString & "'"
        ElseIf rbtChuaDuyet.Checked Then
            query = "Select Distinct TenKhachHang,KhachHangId From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Chưa duyệt' And NgayBan='" & dtpTheoNgay.Value.ToShortDateString & "'"
            query = query & " Union Select Distinct NhanVienTiepThi As TenKhachHang,NhanVienTiepThiID As KhachHangId From DonHangBan Where NhanVienTiepThiId<>0 And Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Chưa duyệt' And NgayBan='" & dtpTheoNgay.Value.ToShortDateString & "'"
        ElseIf rbtDaDuyet.Checked Then
            query = "Select Distinct TenKhachHang,KhachHangId From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Đã duyệt' And NgayBan='" & dtpTheoNgay.Value.ToShortDateString & "'"
            query = query & " Union Select Distinct NhanVienTiepThi As TenKhachHang,NhanVienTiepThiID As KhachHangId From DonHangBan Where NhanVienTiepThiId<>0 And Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Đã duyệt' And NgayBan='" & dtpTheoNgay.Value.ToShortDateString & "'"
        End If
        adapter = New OleDb.OleDbDataAdapter(query, dbConn)
        adapter.Fill(dtTenKhachHang)
        Dim TongPhiVanChuyen As Single = 0
        For i As Integer = 0 To dtTenKhachHang.Rows.Count - 1
            Dim titleRow As DataRow
            titleRow = groupingList.NewRow
            titleRow.Item("TenKhachHang") = dtTenKhachHang.Rows(i)("TenKhachHang")
            titleRow.Item("MaSoThue") = ""
            titleRow.Item("DiaChi") = ""
            titleRow.Item("NgayBan") = ""
            titleRow.Item("So") = ""
            titleRow.Item("PhiVanChuyen") = ""
            groupingList.Rows.Add(titleRow)
            'detail
            If rbtTatCa.Checked Then
                query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai<>N'Đã hủy' And KhachHangId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And NgayBan='" & dtpTheoNgay.Value.ToShortDateString & "' order by ngay_so desc, cast(so as int) desc"
            ElseIf rbtChuaDuyet.Checked Then
                query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Chưa duyệt' And KhachHangId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And NgayBan='" & dtpTheoNgay.Value.ToShortDateString & "' order by ngay_so desc, cast(so as int) desc"
            ElseIf rbtDaDuyet.Checked Then
                query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Đã duyệt' And KhachHangId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And NgayBan='" & dtpTheoNgay.Value.ToShortDateString & "' order by ngay_so desc, cast(so as int) desc"
            End If

            Dim dtThongKe As New DataTable
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtThongKe)
            TongPhiVanChuyen = 0
            For j As Integer = 0 To dtThongKe.Rows.Count - 1
                formatDate2VN(dtThongKe.Rows(j)("NgayBan"))
                'detail row
                Dim detailRow As DataRow
                detailRow = groupingList.NewRow
                detailRow.Item("TenKhachHang") = ""
                detailRow.Item("MaSoThue") = dtThongKe.Rows(j)("MaSoThue")
                detailRow.Item("DiaChi") = dtThongKe.Rows(j)("DiaChi")
                detailRow.Item("NgayBan") = dtThongKe.Rows(j)("NgayBan")
                detailRow.Item("So") = dtThongKe.Rows(j)("So")
                detailRow.Item("PhiVanChuyen") = Format(dtThongKe.Rows(j)("PhiVanChuyen"), "N02")
                detailRow.Item("Loai") = "Mua hàng"
                groupingList.Rows.Add(detailRow)
                TongPhiVanChuyen = TongPhiVanChuyen + Single.Parse(dtThongKe.Rows(j)("PhiVanChuyen"))
            Next
            'Đại lý tiếp thị
            If rbtTatCa.Checked Then
                query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai<>N'Đã hủy' And NhanVienTiepThiId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And NgayBan='" & dtpTheoNgay.Value.ToShortDateString & "' order by ngay_so desc, cast(so as int) desc"
            ElseIf rbtChuaDuyet.Checked Then
                query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Chưa duyệt' And NhanVienTiepThiId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And NgayBan='" & dtpTheoNgay.Value.ToShortDateString & "' order by ngay_so desc, cast(so as int) desc"
            ElseIf rbtDaDuyet.Checked Then
                query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Đã duyệt' And NhanVienTiepThiId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And NgayBan='" & dtpTheoNgay.Value.ToShortDateString & "' order by ngay_so desc, cast(so as int) desc"
            End If
            dtThongKe = New DataTable
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtThongKe)
            For j As Integer = 0 To dtThongKe.Rows.Count - 1
                formatDate2VN(dtThongKe.Rows(j)("NgayBan"))
                'detail row
                Dim detailRow As DataRow
                detailRow = groupingList.NewRow
                detailRow.Item("TenKhachHang") = ""
                detailRow.Item("MaSoThue") = dtThongKe.Rows(j)("MaSoThue")
                detailRow.Item("DiaChi") = dtThongKe.Rows(j)("DiaChi")
                detailRow.Item("NgayBan") = dtThongKe.Rows(j)("NgayBan")
                detailRow.Item("So") = dtThongKe.Rows(j)("So")
                detailRow.Item("PhiVanChuyen") = Format(dtThongKe.Rows(j)("PhiVanChuyen"), "N02")
                detailRow.Item("Loai") = "Đại lý tiếp thị"
                groupingList.Rows.Add(detailRow)
                TongPhiVanChuyen = TongPhiVanChuyen + Single.Parse(dtThongKe.Rows(j)("PhiVanChuyen"))
            Next
            Dim SumRow As DataRow
            SumRow = groupingList.NewRow
            SumRow.Item("TenKhachHang") = ""
            SumRow.Item("MaSoThue") = ""
            SumRow.Item("DiaChi") = ""
            SumRow.Item("NgayBan") = ""
            SumRow.Item("So") = "Tổng :"
            SumRow.Item("PhiVanChuyen") = Format(TongPhiVanChuyen, "N02")  ' CDec(TongDoanhThu.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
            groupingList.Rows.Add(SumRow)
            'update row i
        Next
        dbConn.Close()
        'bind data
        BindData(groupingList)
    End Sub
#End Region
#Region "Report Theo Tháng"
    Private Sub Report_TheoThang()
        Dim dbConn As New OleDb.OleDbConnection
        Dim dataAccess As New DataAccess
        Dim adapter As OleDb.OleDbDataAdapter
        dataAccess.ConnectDatabase_DBAccess(dbConn)
        Dim Thang As MonthInfo
        Thang = CType(cbxTheoThang.SelectedItem, MonthInfo)
        If Thang Is Nothing Then
            MessageBox.Show("Bạn chưa chọn tháng.Vui lòng kiểm tra lại!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If
        Dim Nam As YearInfo
        Nam = CType(cbxNamThang.SelectedItem, YearInfo)
        If Nam Is Nothing Then
            MessageBox.Show("Bạn chưa chọn năm.Vui lòng kiểm tra lại!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If
        Dim query As String = ""
        REM Grouping list
        groupingList = Create_GroupingList()
        'title
        Dim dtTenKhachHang As New DataTable
        If rbtTatCa.Checked Then
            query = "Select Distinct TenKhachHang,KhachHangId From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai<>N'Đã hủy' And Thang='" & Thang.TenThang & "' And Nam='" & Nam.TenNam & "'"
            query = query & " Union Select Distinct NhanVienTiepThi As TenKhachHang,NhanVienTiepThiID As KhachHangId From DonHangBan Where NhanVienTiepThiId<>0 And  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai<>N'Đã hủy' And Thang='" & Thang.TenThang & "' And Nam='" & Nam.TenNam & "'"
        ElseIf rbtChuaDuyet.Checked Then
            query = "Select Distinct TenKhachHang,KhachHangId From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Chưa duyệt' And Thang='" & Thang.TenThang & "' And Nam='" & Nam.TenNam & "'"
            query = query & " Union Select Distinct NhanVienTiepThi As TenKhachHang,NhanVienTiepThiID As KhachHangId From DonHangBan Where NhanVienTiepThiId<>0 And  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Chưa duyệt' And Thang='" & Thang.TenThang & "' And Nam='" & Nam.TenNam & "'"
        ElseIf rbtDaDuyet.Checked Then
            query = "Select Distinct TenKhachHang,KhachHangId From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Đã duyệt' And Thang='" & Thang.TenThang & "' And Nam='" & Nam.TenNam & "'"
            query = query & " Union Select Distinct NhanVienTiepThi As TenKhachHang,NhanVienTiepThiID As KhachHangId From DonHangBan Where NhanVienTiepThiId<>0 And  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Đã duyệt' And Thang='" & Thang.TenThang & "' And Nam='" & Nam.TenNam & "'"
        End If
        adapter = New OleDb.OleDbDataAdapter(query, dbConn)
        adapter.Fill(dtTenKhachHang)
        Dim TongPhiVanChuyen As Single = 0
        For i As Integer = 0 To dtTenKhachHang.Rows.Count - 1
            ''title
            Dim titleRow As DataRow
            titleRow = groupingList.NewRow
            titleRow.Item("TenKhachHang") = dtTenKhachHang.Rows(i)("TenKhachHang")
            titleRow.Item("MaSoThue") = ""
            titleRow.Item("DiaChi") = ""
            titleRow.Item("NgayBan") = ""
            titleRow.Item("So") = ""
            titleRow.Item("PhiVanChuyen") = ""
            groupingList.Rows.Add(titleRow)
            'detail
            If rbtTatCa.Checked Then
                query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai<>N'Đã hủy' And KhachHangId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And Thang='" & Thang.TenThang & "' And Nam='" & Nam.TenNam & "' Order By Ngay_So DESC"
            ElseIf rbtChuaDuyet.Checked Then
                query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Chưa duyệt' And KhachHangId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And Thang='" & Thang.TenThang & "' And Nam='" & Nam.TenNam & "' Order By Ngay_So DESC"
            ElseIf rbtDaDuyet.Checked Then
                query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Đã duyệt' And KhachHangId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And Thang='" & Thang.TenThang & "' And Nam='" & Nam.TenNam & "' Order By Ngay_So DESC"
            End If
            Dim dtThongKe As New DataTable
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtThongKe)
            TongPhiVanChuyen = 0
            For j As Integer = 0 To dtThongKe.Rows.Count - 1
                formatDate2VN(dtThongKe.Rows(j)("NgayBan"))
                'detail row
                Dim detailRow As DataRow
                detailRow = groupingList.NewRow
                detailRow.Item("TenKhachHang") = ""
                detailRow.Item("MaSoThue") = dtThongKe.Rows(j)("MaSoThue")
                detailRow.Item("DiaChi") = dtThongKe.Rows(j)("DiaChi")
                detailRow.Item("NgayBan") = dtThongKe.Rows(j)("NgayBan")
                detailRow.Item("So") = dtThongKe.Rows(j)("So")
                detailRow.Item("PhiVanChuyen") = dtThongKe.Rows(j)("PhiVanChuyen")
                detailRow.Item("Loai") = "Mua hàng"
                groupingList.Rows.Add(detailRow)
                TongPhiVanChuyen = TongPhiVanChuyen + Single.Parse(dtThongKe.Rows(j)("PhiVanChuyen"))
            Next
            'Đại lý tiep thi
            'detail
            If rbtTatCa.Checked Then
                query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai<>N'Đã hủy' And NhanVienTiepThiId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And Thang='" & Thang.TenThang & "' And Nam='" & Nam.TenNam & "' Order By Ngay_So DESC"
            ElseIf rbtChuaDuyet.Checked Then
                query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Chưa duyệt' And NhanVienTiepThiId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And Thang='" & Thang.TenThang & "' And Nam='" & Nam.TenNam & "' Order By Ngay_So DESC"
            ElseIf rbtDaDuyet.Checked Then
                query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Đã duyệt' And NhanVienTiepThiId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And Thang='" & Thang.TenThang & "' And Nam='" & Nam.TenNam & "' Order By Ngay_So DESC"
            End If
            dtThongKe = New DataTable
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtThongKe)
            For j As Integer = 0 To dtThongKe.Rows.Count - 1
                formatDate2VN(dtThongKe.Rows(j)("NgayBan"))
                'detail row
                Dim detailRow As DataRow
                detailRow = groupingList.NewRow
                detailRow.Item("TenKhachHang") = ""
                detailRow.Item("MaSoThue") = dtThongKe.Rows(j)("MaSoThue")
                detailRow.Item("DiaChi") = dtThongKe.Rows(j)("DiaChi")
                detailRow.Item("NgayBan") = dtThongKe.Rows(j)("NgayBan")
                detailRow.Item("So") = dtThongKe.Rows(j)("So")
                detailRow.Item("PhiVanChuyen") = dtThongKe.Rows(j)("PhiVanChuyen")
                detailRow.Item("Loai") = "Đại lý tiếp thị"
                groupingList.Rows.Add(detailRow)
                TongPhiVanChuyen = TongPhiVanChuyen + Single.Parse(dtThongKe.Rows(j)("PhiVanChuyen"))
            Next
            Dim SumRow As DataRow
            SumRow = groupingList.NewRow
            SumRow.Item("TenKhachHang") = ""
            SumRow.Item("MaSoThue") = ""
            SumRow.Item("DiaChi") = ""
            SumRow.Item("NgayBan") = ""
            SumRow.Item("So") = "Tổng :"
            SumRow.Item("PhiVanChuyen") = Format(TongPhiVanChuyen, "N02") 'CDec(TongDoanhThu.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
            groupingList.Rows.Add(SumRow)
            'update row i
        Next
        dbConn.Close()
        'bind data
        BindData(groupingList)
    End Sub
#End Region
#Region "Report theo năm"
    Sub Report_TheoNam()
        Dim dbConn As New OleDb.OleDbConnection
        Dim dataAccess As New DataAccess
        Dim adapter As OleDb.OleDbDataAdapter
        dataAccess.ConnectDatabase_DBAccess(dbConn)
        Dim Nam As YearInfo
        Nam = CType(cbxTheoNam.SelectedItem, YearInfo)
        If Nam Is Nothing Then
            MessageBox.Show("Bạn chưa chọn năm.Vui lòng kiểm tra lại!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If
        Dim query As String = ""
        REM Grouping list
        groupingList = Create_GroupingList()
        'title
        Dim dtTenKhachHang As New DataTable
        If rbtTatCa.Checked Then
            query = "Select Distinct TenKhachHang,KhachHangId From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai<>N'Đã hủy' And Nam='" & Nam.TenNam & "'"
            query = query & " Union Select Distinct NhanVienTiepThi As TenKhachHang,NhanVienTiepThiID AS KhachHangId From DonHangBan Where NhanVienTiepThiID<>0 And Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai<>N'Đã hủy' And Nam='" & Nam.TenNam & "'"
        ElseIf rbtChuaDuyet.Checked Then
            query = "Select Distinct TenKhachHang,KhachHangId From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Chưa duyệt' And Nam='" & Nam.TenNam & "'"
            query = query & " Union Select Distinct NhanVienTiepThi As TenKhachHang,NhanVienTiepThiID AS KhachHangId From DonHangBan Where NhanVienTiepThiID<>0 And Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Chưa duyệt' And Nam='" & Nam.TenNam & "'"
        ElseIf rbtDaDuyet.Checked Then
            query = "Select Distinct TenKhachHang,KhachHangId From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Đã duyệt' And Nam='" & Nam.TenNam & "'"
            query = query & " Union Select Distinct NhanVienTiepThi As TenKhachHang,NhanVienTiepThiID AS KhachHangId From DonHangBan Where NhanVienTiepThiID<>0 And Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Đã duyệt' And Nam='" & Nam.TenNam & "'"
        End If
        adapter = New OleDb.OleDbDataAdapter(query, dbConn)
        adapter.Fill(dtTenKhachHang)
        Dim TongPhiVanChuyen As Single = 0
        For i As Integer = 0 To dtTenKhachHang.Rows.Count - 1
            ''title
            Dim titleRow As DataRow
            titleRow = groupingList.NewRow
            titleRow.Item("TenKhachHang") = dtTenKhachHang.Rows(i)("TenKhachHang")
            titleRow.Item("MaSoThue") = ""
            titleRow.Item("DiaChi") = ""
            titleRow.Item("NgayBan") = ""
            titleRow.Item("So") = ""
            titleRow.Item("PhiVanChuyen") = ""
            groupingList.Rows.Add(titleRow)
            'detail
            If rbtTatCa.Checked Then
                query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai<>N'Đã hủy' And KhachHangId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And Nam='" & Nam.TenNam & "' Order By Ngay_So DESC"
            ElseIf rbtChuaDuyet.Checked Then
                query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Chưa duyệt' And KhachHangId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And And Nam='" & Nam.TenNam & "' Order By Ngay_So DESC"
            ElseIf rbtDaDuyet.Checked Then
                query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Đã duyệt' And KhachHangId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And Nam='" & Nam.TenNam & "' Order By Ngay_So DESC"
            End If
            Dim dtThongKe As New DataTable
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtThongKe)
            TongPhiVanChuyen = 0
            For j As Integer = 0 To dtThongKe.Rows.Count - 1
                formatDate2VN(dtThongKe.Rows(j)("NgayBan"))
                'detail row
                Dim detailRow As DataRow
                detailRow = groupingList.NewRow
                detailRow.Item("TenKhachHang") = ""
                detailRow.Item("MaSoThue") = dtThongKe.Rows(j)("MaSoThue")
                detailRow.Item("DiaChi") = dtThongKe.Rows(j)("DiaChi")
                detailRow.Item("NgayBan") = dtThongKe.Rows(j)("NgayBan")
                detailRow.Item("So") = dtThongKe.Rows(j)("So")
                detailRow.Item("PhiVanChuyen") = dtThongKe.Rows(j)("PhiVanChuyen")
                detailRow.Item("Loai") = "Mua hàng"
                groupingList.Rows.Add(detailRow)
                TongPhiVanChuyen = TongPhiVanChuyen + Single.Parse(dtThongKe.Rows(j)("PhiVanChuyen"))
            Next
            'dai ly tiep thi
            '    'detail
            If rbtTatCa.Checked Then
                query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai<>N'Đã hủy' And NhanVienTiepThiID=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And Nam='" & Nam.TenNam & "' Order By Ngay_So DESC"
            ElseIf rbtChuaDuyet.Checked Then
                query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Chưa duyệt' And NhanVienTiepThiID=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And And Nam='" & Nam.TenNam & "' Order By Ngay_So DESC"
            ElseIf rbtDaDuyet.Checked Then
                query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Đã duyệt' And NhanVienTiepThiID=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And Nam='" & Nam.TenNam & "' Order By Ngay_So DESC"
            End If
            dtThongKe = New DataTable
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtThongKe)
            For j As Integer = 0 To dtThongKe.Rows.Count - 1
                formatDate2VN(dtThongKe.Rows(j)("NgayBan"))
                'detail row
                Dim detailRow As DataRow
                detailRow = groupingList.NewRow
                detailRow.Item("TenKhachHang") = ""
                detailRow.Item("MaSoThue") = dtThongKe.Rows(j)("MaSoThue")
                detailRow.Item("DiaChi") = dtThongKe.Rows(j)("DiaChi")
                detailRow.Item("NgayBan") = dtThongKe.Rows(j)("NgayBan")
                detailRow.Item("So") = dtThongKe.Rows(j)("So")
                detailRow.Item("PhiVanChuyen") = dtThongKe.Rows(j)("PhiVanChuyen")
                detailRow.Item("Loai") = "Đại lý tiếp thị"
                groupingList.Rows.Add(detailRow)
                TongPhiVanChuyen = TongPhiVanChuyen + Single.Parse(dtThongKe.Rows(j)("PhiVanChuyen"))
            Next
            Dim SumRow As DataRow
            SumRow = groupingList.NewRow
            SumRow.Item("TenKhachHang") = ""
            SumRow.Item("MaSoThue") = ""
            SumRow.Item("DiaChi") = ""
            SumRow.Item("NgayBan") = ""
            SumRow.Item("So") = "Tổng :"
            SumRow.Item("PhiVanChuyen") = Format(TongPhiVanChuyen, "N02") 'CDec(TongDoanhThu.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
            groupingList.Rows.Add(SumRow)
            'update row i
        Next
        dbConn.Close()
        'bind data
        BindData(groupingList)
    End Sub
#End Region
#Region "Report từ ngày đến ngày"
    Sub Report_TuNgayDenNgay()
        '2010007004  -->20100704
        Dim TuNgay As Integer = ConvertDateToInt(dtpTuNgay.Value.ToString.Split(" ")(0))
        Dim DenNgay As Integer = ConvertDateToInt(dtpDenNgay.Value.ToString.Split(" ")(0))
        If DenNgay < TuNgay Then
            Dim dlgRes As DialogResult
            dlgRes = MessageBox.Show( _
                      "Ngày đến không thể nhỏ hơn từ ngày.Vui lòng nhập lại", _
                      "Thông báo", _
                MessageBoxButtons.OK, _
                      MessageBoxIcon.Question)
            If dlgRes = Windows.Forms.DialogResult.OK Then
                Exit Sub
            End If
        End If
        Dim dbConn As New OleDb.OleDbConnection
        Dim dataAccess As New DataAccess
        Dim adapter As OleDb.OleDbDataAdapter
        dataAccess.ConnectDatabase_DBAccess(dbConn)
        Dim query As String = ""
        REM Grouping list
        groupingList = Create_GroupingList()
        groupingList = Create_GroupingList()
        'title
        Dim dtTenKhachHang As New DataTable
        If rbtTatCa.Checked Then
            query = "Select Distinct TenKhachHang,KhachHangId From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai<>N'Đã hủy' And Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay
            query = query & " Union Select Distinct NhanVienTiepThi As TenKhachHang,NhanVienTiepThiID As KhachHangId From DonHangBan Where NhanVienTiepThiID<>0 And Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai<>N'Đã hủy' And Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay
        ElseIf rbtChuaDuyet.Checked Then
            query = "Select Distinct TenKhachHang,KhachHangId From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Chưa duyệt' And  Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay
            query = query & " Union Select Distinct NhanVienTiepThi As TenKhachHang,NhanVienTiepThiID As KhachHangId From DonHangBan Where NhanVienTiepThiID<>0 And Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Chưa duyệt' And  Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay
        ElseIf rbtDaDuyet.Checked Then
            query = "Select Distinct TenKhachHang,KhachHangId From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Đã duyệt' And  Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay
            query = query & " Union Select Distinct NhanVienTiepThi As TenKhachHang,NhanVienTiepThiID As KhachHangId From DonHangBan Where NhanVienTiepThiID<>0 And Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Đã duyệt' And  Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay
        End If
        adapter = New OleDb.OleDbDataAdapter(query, dbConn)
        adapter.Fill(dtTenKhachHang)
        Dim TongPhiVanChuyen As Single = 0
        For i As Integer = 0 To dtTenKhachHang.Rows.Count - 1
            ''title
            Dim titleRow As DataRow
            titleRow = groupingList.NewRow
            titleRow.Item("TenKhachHang") = dtTenKhachHang.Rows(i)("TenKhachHang")
            titleRow.Item("MaSoThue") = ""
            titleRow.Item("DiaChi") = ""
            titleRow.Item("NgayBan") = ""
            titleRow.Item("So") = ""
            titleRow.Item("PhiVanChuyen") = ""
            groupingList.Rows.Add(titleRow)
            'detail
            If rbtTatCa.Checked Then
                query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai<>N'Đã hủy' And KhachHangId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay & " Order By Ngay_So DESC"
            ElseIf rbtChuaDuyet.Checked Then
                query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Chưa duyệt' And KhachHangId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay & " Order By Ngay_So DESC"
            ElseIf rbtDaDuyet.Checked Then
                query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Đã duyệt' And KhachHangId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay & " Order By Ngay_So DESC"
            End If
            Dim dtThongKe As New DataTable
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtThongKe)
            TongPhiVanChuyen = 0
            For j As Integer = 0 To dtThongKe.Rows.Count - 1
                formatDate2VN(dtThongKe.Rows(j)("NgayBan"))
                'detail row
                Dim detailRow As DataRow
                detailRow = groupingList.NewRow
                detailRow.Item("TenKhachHang") = ""
                detailRow.Item("MaSoThue") = dtThongKe.Rows(j)("MaSoThue")
                detailRow.Item("DiaChi") = dtThongKe.Rows(j)("DiaChi")
                detailRow.Item("NgayBan") = dtThongKe.Rows(j)("NgayBan")
                detailRow.Item("So") = dtThongKe.Rows(j)("So")
                detailRow.Item("PhiVanChuyen") = dtThongKe.Rows(j)("PhiVanChuyen")
                detailRow.Item("Loai") = "Mua hàng"
                groupingList.Rows.Add(detailRow)
                TongPhiVanChuyen = TongPhiVanChuyen + Single.Parse(dtThongKe.Rows(j)("PhiVanChuyen"))
            Next
            'detail
            If rbtTatCa.Checked Then
                query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai<>N'Đã hủy' And NhanVienTiepThiID=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay & " Order By Ngay_So DESC"
            ElseIf rbtChuaDuyet.Checked Then
                query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Chưa duyệt' And NhanVienTiepThiID=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay & " Order By Ngay_So DESC"
            ElseIf rbtDaDuyet.Checked Then
                query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Đã duyệt' And NhanVienTiepThiID=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay & " Order By Ngay_So DESC"
            End If
            dtThongKe = New DataTable
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtThongKe)
            For j As Integer = 0 To dtThongKe.Rows.Count - 1
                formatDate2VN(dtThongKe.Rows(j)("NgayBan"))
                'detail row
                Dim detailRow As DataRow
                detailRow = groupingList.NewRow
                detailRow.Item("TenKhachHang") = ""
                detailRow.Item("MaSoThue") = dtThongKe.Rows(j)("MaSoThue")
                detailRow.Item("DiaChi") = dtThongKe.Rows(j)("DiaChi")
                detailRow.Item("NgayBan") = dtThongKe.Rows(j)("NgayBan")
                detailRow.Item("So") = dtThongKe.Rows(j)("So")
                detailRow.Item("PhiVanChuyen") = dtThongKe.Rows(j)("PhiVanChuyen")
                detailRow.Item("Loai") = "Đại lý tiếp thị"
                groupingList.Rows.Add(detailRow)
                TongPhiVanChuyen = TongPhiVanChuyen + Single.Parse(dtThongKe.Rows(j)("PhiVanChuyen"))
            Next
            Dim SumRow As DataRow
            SumRow = groupingList.NewRow
            SumRow.Item("TenKhachHang") = ""
            SumRow.Item("MaSoThue") = ""
            SumRow.Item("DiaChi") = ""
            SumRow.Item("NgayBan") = ""
            SumRow.Item("So") = "Tổng :"
            SumRow.Item("PhiVanChuyen") = Format(TongPhiVanChuyen, "N02") 'CDec(TongDoanhThu.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
            groupingList.Rows.Add(SumRow)
            'update row i
        Next
        dbConn.Close()
        'bind data
        BindData(groupingList)
    End Sub
#End Region
#Region "Report theo tên khách hàng"
    Function IsNotExist(ByVal TenKhachHang As String) As Boolean
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dsKhachHang As New DataSet
            Dim dtKhachHang As New DataTable
            Dim query As String = "Select Count(*) From DanhMucKhachHang Where LOWER(TenKhachHang)=N'" & TenKhachHang & "'"
            Dim dbCommand As OleDb.OleDbCommand
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            dbCommand = New OleDb.OleDbCommand(query, dbConn)
            Dim iResult As Integer
            iResult = dbCommand.ExecuteScalar()
            If iResult = 0 Then
                Return True
            Else
                Return False
            End If
            dbConn.Close()
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Function
    Private Sub Report_TheoTenKhachHang()
        Dim dbConn As New OleDb.OleDbConnection
        Dim dataAccess As New DataAccess
        Dim adapter As OleDb.OleDbDataAdapter
        dataAccess.ConnectDatabase_DBAccess(dbConn)
        'check Thông tin Khách Hàng
        'End Check
        Dim query As String = ""
        REM Grouping list
        groupingList = Create_GroupingList()
        'title
        Dim dtTenKhachHang As New DataTable
        If lblKhachHangId.Text = "0" Or lblKhachHangId.Text = "" Then
            If rbtTatCa.Checked Then
                query = "Select Distinct TenKhachHang,KhachHangId From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai<>N'Đã hủy' And TenKhachHang Like '" & txtTenKhachHang.Text.Trim & "'"
                query = query & " Union Select Distinct NhanVienTiepThi AS TenKhachHang,NhanVienTiepThiID As KhachHangId From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai<>N'Đã hủy' And NhanVienTiepThi Like '" & txtTenKhachHang.Text.Trim & "'"
            ElseIf rbtChuaDuyet.Checked Then
                query = "Select Distinct TenKhachHang,KhachHangId From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Chưa duyệt' And  TenKhachHang Like '" & txtTenKhachHang.Text.Trim & "'"
                query = query & " Union Select Distinct NhanVienTiepThi AS TenKhachHang,NhanVienTiepThiID As KhachHangId From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Chưa duyệt' And  NhanVienTiepThi Like '" & txtTenKhachHang.Text.Trim & "'"
            ElseIf rbtDaDuyet.Checked Then
                query = "Select Distinct TenKhachHang,KhachHangId From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Đã duyệt' And  TenKhachHang Like '" & txtTenKhachHang.Text.Trim & "'"
                query = query & " Union Select Distinct NhanVienTiepThi AS TenKhachHang,NhanVienTiepThiID As KhachHangId From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Đã duyệt' And  NhanVienTiepThi Like '" & txtTenKhachHang.Text.Trim & "'"
            End If
        Else
            If rbtTatCa.Checked Then
                query = "Select Distinct TenKhachHang,KhachHangId From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai<>N'Đã hủy' And KhachHangId=" & lblKhachHangId.Text.Trim
                query = query & " Union Select Distinct NhanVienTiepThi AS TenKhachHang,NhanVienTiepThiID As KhachHangId From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai<>N'Đã hủy' And NhanVienTiepThiID=" & lblKhachHangId.Text.Trim
            ElseIf rbtChuaDuyet.Checked Then
                query = "Select Distinct TenKhachHang,KhachHangId From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Chưa duyệt' And  KhachHangId=" & lblKhachHangId.Text.Trim
                query = query & " Union Select Distinct NhanVienTiepThi AS TenKhachHang,NhanVienTiepThiID As KhachHangId From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Chưa duyệt' And  NhanVienTiepThiID=" & lblKhachHangId.Text.Trim
            ElseIf rbtDaDuyet.Checked Then
                query = "Select Distinct TenKhachHang,KhachHangId From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Đã duyệt' And  KhachHangId=" & lblKhachHangId.Text.Trim
                query = query & " Union Select Distinct NhanVienTiepThi AS TenKhachHang,NhanVienTiepThiID As KhachHangId From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Đã duyệt' And  NhanVienTiepThiID=" & lblKhachHangId.Text.Trim
            End If
        End If
        adapter = New OleDb.OleDbDataAdapter(query, dbConn)
        adapter.Fill(dtTenKhachHang)
        Dim TongPhiVanChuyen As Single = 0
        For i As Integer = 0 To dtTenKhachHang.Rows.Count - 1
            'title
            Dim titleRow As DataRow
            titleRow = groupingList.NewRow
            titleRow.Item("TenKhachHang") = dtTenKhachHang.Rows(i)("TenKhachHang")
            titleRow.Item("MaSoThue") = ""
            titleRow.Item("DiaChi") = ""
            titleRow.Item("NgayBan") = ""
            titleRow.Item("So") = ""
            titleRow.Item("PhiVanChuyen") = ""
            groupingList.Rows.Add(titleRow)
            'detail
            If lblKhachHangId.Text = "0" Or lblKhachHangId.Text = "" Then
                If rbtTatCa.Checked Then
                    query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai<>N'Đã hủy' And KhachHangId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And TenKhachHang Like '" & txtTenKhachHang.Text.Trim & "' Order By Ngay_So DESC"
                ElseIf rbtChuaDuyet.Checked Then
                    query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Chưa duyệt' And KhachHangId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And TenKhachHang Like '" & txtTenKhachHang.Text.Trim & "' Order By Ngay_So DESC"
                ElseIf rbtDaDuyet.Checked Then
                    query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Đã duyệt' And KhachHangId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And TenKhachHang Like '" & txtTenKhachHang.Text.Trim & "' Order By Ngay_So DESC"
                End If
            Else
                If rbtTatCa.Checked Then
                    query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai<>N'Đã hủy' And KhachHangId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And KhachHangId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And KhachHangId=" & lblKhachHangId.Text.Trim & " Order By Ngay_So DESC"
                ElseIf rbtChuaDuyet.Checked Then
                    query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Chưa duyệt' And KhachHangId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And KhachHangId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And KhachHangId=" & lblKhachHangId.Text.Trim & " Order By Ngay_So DESC"
                ElseIf rbtDaDuyet.Checked Then
                    query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Đã duyệt' And KhachHangId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And KhachHangId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And KhachHangId=" & lblKhachHangId.Text.Trim & " Order By Ngay_So DESC"
                End If
            End If
            Dim dtThongKe As New DataTable
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtThongKe)
            For j As Integer = 0 To dtThongKe.Rows.Count - 1
                formatDate2VN(dtThongKe.Rows(j)("NgayBan"))
                'detail row
                Dim detailRow As DataRow
                detailRow = groupingList.NewRow
                detailRow.Item("TenKhachHang") = ""
                detailRow.Item("MaSoThue") = dtThongKe.Rows(j)("MaSoThue")
                detailRow.Item("DiaChi") = dtThongKe.Rows(j)("DiaChi")
                detailRow.Item("NgayBan") = dtThongKe.Rows(j)("NgayBan")
                detailRow.Item("So") = dtThongKe.Rows(j)("So")
                detailRow.Item("PhiVanChuyen") = dtThongKe.Rows(j)("PhiVanChuyen")
                detailRow.Item("Loai") = "Mua hàng"
                groupingList.Rows.Add(detailRow)
                TongPhiVanChuyen = TongPhiVanChuyen + Single.Parse(dtThongKe.Rows(j)("PhiVanChuyen"))
            Next
            'detail
            If lblKhachHangId.Text = "0" Or lblKhachHangId.Text = "" Then
                If rbtTatCa.Checked Then
                    query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai<>N'Đã hủy' And NhanVienTiepThiID=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And TenNhanVienTiepThi Like '" & txtTenKhachHang.Text.Trim & "' Order By Ngay_So DESC"
                ElseIf rbtChuaDuyet.Checked Then
                    query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Chưa duyệt' And NhanVienTiepThiID=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And TenNhanVienTiepThi Like '" & txtTenKhachHang.Text.Trim & "' Order By Ngay_So DESC"
                ElseIf rbtDaDuyet.Checked Then
                    query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Đã duyệt' And NhanVienTiepThiID=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And TenNhanVienTiepThi Like '" & txtTenKhachHang.Text.Trim & "' Order By Ngay_So DESC"
                End If
            Else
                If rbtTatCa.Checked Then
                    query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai<>N'Đã hủy' And NhanVienTiepThiID=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And NhanVienTiepThiID=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And NhanVienTiepThiID=" & lblKhachHangId.Text.Trim & " Order By Ngay_So DESC"
                ElseIf rbtChuaDuyet.Checked Then
                    query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Chưa duyệt' And NhanVienTiepThiID=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And NhanVienTiepThiID=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And NhanVienTiepThiID=" & lblKhachHangId.Text.Trim & " Order By Ngay_So DESC"
                ElseIf rbtDaDuyet.Checked Then
                    query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Đã duyệt' And NhanVienTiepThiID=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And NhanVienTiepThiID=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And NhanVienTiepThiID=" & lblKhachHangId.Text.Trim & " Order By Ngay_So DESC"
                End If
            End If

            dtThongKe = New DataTable
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtThongKe)
            For j As Integer = 0 To dtThongKe.Rows.Count - 1
                formatDate2VN(dtThongKe.Rows(j)("NgayBan"))
                'detail row
                Dim detailRow As DataRow
                detailRow = groupingList.NewRow
                detailRow.Item("TenKhachHang") = ""
                detailRow.Item("MaSoThue") = dtThongKe.Rows(j)("MaSoThue")
                detailRow.Item("DiaChi") = dtThongKe.Rows(j)("DiaChi")
                detailRow.Item("NgayBan") = dtThongKe.Rows(j)("NgayBan")
                detailRow.Item("So") = dtThongKe.Rows(j)("So")
                detailRow.Item("PhiVanChuyen") = dtThongKe.Rows(j)("PhiVanChuyen")
                detailRow.Item("Loai") = "Đại lý tiếp thị"
                groupingList.Rows.Add(detailRow)
                TongPhiVanChuyen = TongPhiVanChuyen + Single.Parse(dtThongKe.Rows(j)("PhiVanChuyen"))
            Next
            Dim SumRow As DataRow
            SumRow = groupingList.NewRow
            SumRow.Item("TenKhachHang") = ""
            SumRow.Item("MaSoThue") = ""
            SumRow.Item("DiaChi") = ""
            SumRow.Item("NgayBan") = ""
            SumRow.Item("So") = "Tổng :"
            SumRow.Item("PhiVanChuyen") = Format(TongPhiVanChuyen, "N02")
            groupingList.Rows.Add(SumRow)
            'update row i
        Next



        'Đại lý tiếp thị
        ''title
        'dtTenKhachHang = New DataTable
        'If lblKhachHangId.Text = "0" Or lblKhachHangId.Text = "" Then
        '    If rbtTatCa.Checked Then
        '        query = "Select Distinct NhanVienTiepThi AS TenKhachHang,NhanVienTiepThiID As KhachHangId From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai<>N'Đã hủy' And NhanVienTiepThi Like '" & txtTenKhachHang.Text.Trim & "'"
        '    ElseIf rbtChuaDuyet.Checked Then
        '        query = "Select Distinct NhanVienTiepThi AS TenKhachHang,NhanVienTiepThiID As KhachHangId From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Chưa duyệt' And  NhanVienTiepThi Like '" & txtTenKhachHang.Text.Trim & "'"
        '    ElseIf rbtDaDuyet.Checked Then
        '        query = "Select Distinct NhanVienTiepThi AS TenKhachHang,NhanVienTiepThiID As KhachHangId From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Đã duyệt' And  NhanVienTiepThi Like '" & txtTenKhachHang.Text.Trim & "'"
        '    End If
        'Else
        '    If rbtTatCa.Checked Then
        '        query = "Select Distinct NhanVienTiepThi AS TenKhachHang,NhanVienTiepThiID As KhachHangId From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai<>N'Đã hủy' And NhanVienTiepThiID=" & lblKhachHangId.Text.Trim
        '    ElseIf rbtChuaDuyet.Checked Then
        '        query = "Select Distinct NhanVienTiepThi AS TenKhachHang,NhanVienTiepThiID As KhachHangId From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Chưa duyệt' And  NhanVienTiepThiID=" & lblKhachHangId.Text.Trim
        '    ElseIf rbtDaDuyet.Checked Then
        '        query = "Select Distinct NhanVienTiepThi AS TenKhachHang,NhanVienTiepThiID As KhachHangId From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Đã duyệt' And  NhanVienTiepThiID=" & lblKhachHangId.Text.Trim
        '    End If
        'End If
        'adapter = New OleDb.OleDbDataAdapter(query, dbConn)
        'adapter.Fill(dtTenKhachHang)
        'TongPhiVanChuyen = 0
        'For i As Integer = 0 To dtTenKhachHang.Rows.Count - 1
        '    'title
        '    Dim titleRow As DataRow
        '    titleRow = groupingList.NewRow
        '    titleRow.Item("TenKhachHang") = dtTenKhachHang.Rows(i)("TenKhachHang")
        '    titleRow.Item("MaSoThue") = ""
        '    titleRow.Item("DiaChi") = ""
        '    titleRow.Item("NgayBan") = ""
        '    titleRow.Item("So") = ""
        '    titleRow.Item("PhiVanChuyen") = ""
        '    groupingList.Rows.Add(titleRow)
        '    'detail
        '    If lblKhachHangId.Text = "0" Or lblKhachHangId.Text = "" Then
        '        If rbtTatCa.Checked Then
        '            query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai<>N'Đã hủy' And NhanVienTiepThiID=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And TenNhanVienTiepThi Like '" & txtTenKhachHang.Text.Trim & "' Order By Ngay_So DESC"
        '        ElseIf rbtChuaDuyet.Checked Then
        '            query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Chưa duyệt' And NhanVienTiepThiID=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And TenNhanVienTiepThi Like '" & txtTenKhachHang.Text.Trim & "' Order By Ngay_So DESC"
        '        ElseIf rbtDaDuyet.Checked Then
        '            query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Đã duyệt' And NhanVienTiepThiID=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And TenNhanVienTiepThi Like '" & txtTenKhachHang.Text.Trim & "' Order By Ngay_So DESC"
        '        End If
        '    Else
        '        If rbtTatCa.Checked Then
        '            query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai<>N'Đã hủy' And NhanVienTiepThiID=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And NhanVienTiepThiID=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And NhanVienTiepThiID=" & lblKhachHangId.Text.Trim & " Order By Ngay_So DESC"
        '        ElseIf rbtChuaDuyet.Checked Then
        '            query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Chưa duyệt' And NhanVienTiepThiID=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And NhanVienTiepThiID=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And NhanVienTiepThiID=" & lblKhachHangId.Text.Trim & " Order By Ngay_So DESC"
        '        ElseIf rbtDaDuyet.Checked Then
        '            query = "Select MaSoThue,DiaChi,NgayBan,So,PhiVanChuyen From DonHangBan Where  Xoa=0 And Tien='VND' And HoaDonTang=0 And PhiVanChuyen>0 And TrangThai=N'Đã duyệt' And NhanVienTiepThiID=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And NhanVienTiepThiID=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And NhanVienTiepThiID=" & lblKhachHangId.Text.Trim & " Order By Ngay_So DESC"
        '        End If
        '    End If

        '    Dim dtThongKe As New DataTable
        '    adapter = New OleDb.OleDbDataAdapter(query, dbConn)
        '    adapter.Fill(dtThongKe)
        '    For j As Integer = 0 To dtThongKe.Rows.Count - 1
        '        formatDate2VN(dtThongKe.Rows(j)("NgayBan"))
        '        'detail row
        '        Dim detailRow As DataRow
        '        detailRow = groupingList.NewRow
        '        detailRow.Item("TenKhachHang") = ""
        '        detailRow.Item("MaSoThue") = dtThongKe.Rows(j)("MaSoThue")
        '        detailRow.Item("DiaChi") = dtThongKe.Rows(j)("DiaChi")
        '        detailRow.Item("NgayBan") = dtThongKe.Rows(j)("NgayBan")
        '        detailRow.Item("So") = dtThongKe.Rows(j)("So")
        '        detailRow.Item("PhiVanChuyen") = dtThongKe.Rows(j)("PhiVanChuyen")
        '        detailRow.Item("Loai") = "Đại lý tiếp thị"
        '        groupingList.Rows.Add(detailRow)
        '        TongPhiVanChuyen = TongPhiVanChuyen + Single.Parse(dtThongKe.Rows(j)("PhiVanChuyen"))
        '    Next
        '    Dim SumRow As DataRow
        '    SumRow = groupingList.NewRow
        '    SumRow.Item("TenKhachHang") = ""
        '    SumRow.Item("MaSoThue") = ""
        '    SumRow.Item("DiaChi") = ""
        '    SumRow.Item("NgayBan") = ""
        '    SumRow.Item("So") = "Tổng :"
        '    SumRow.Item("PhiVanChuyen") = Format(TongPhiVanChuyen, "N02")
        '    groupingList.Rows.Add(SumRow)
        '    'update row i
        'Next
        dbConn.Close()
        'bind data
        BindData(groupingList)
    End Sub
#End Region
#Region "Bind Data"
    Protected Sub BindData(ByVal data As DataTable)
        Try
            Dim dataSource As New DataTable
            dataSource = data
            dataSource.DefaultView.Sort = "TenKhachHang DESC"
            'bind data
            dgvThongKeDoanhThu.Rows.Clear()
            dgvThongKeDoanhThu.ColumnsCount = 7
            'tạo header 
            dgvThongKeDoanhThu.FixedRows = 1
            dgvThongKeDoanhThu.Rows.Insert(0)
            dgvThongKeDoanhThu(0, 0) = New SourceGrid.Cells.ColumnHeader("Tên khách hàng")
            dgvThongKeDoanhThu(0, 0).Column.Width = 110
            dgvThongKeDoanhThu(0, 1) = New SourceGrid.Cells.ColumnHeader("Mã số thuế")
            dgvThongKeDoanhThu(0, 1).Column.Width = 100
            dgvThongKeDoanhThu(0, 2) = New SourceGrid.Cells.ColumnHeader("Địa chỉ")
            dgvThongKeDoanhThu(0, 2).Column.Width = 380
            dgvThongKeDoanhThu(0, 3) = New SourceGrid.Cells.ColumnHeader("Ngày bán")
            dgvThongKeDoanhThu(0, 3).Column.Width = 70
            dgvThongKeDoanhThu(0, 4) = New SourceGrid.Cells.ColumnHeader("Số đơn hàng")
            dgvThongKeDoanhThu(0, 4).Column.Width = 80
            dgvThongKeDoanhThu(0, 5) = New SourceGrid.Cells.ColumnHeader("Phí vận chuyển")
            dgvThongKeDoanhThu(0, 5).Column.Width = 100
            dgvThongKeDoanhThu(0, 6) = New SourceGrid.Cells.ColumnHeader("Hình thức")
            dgvThongKeDoanhThu(0, 6).Column.Width = 100
            'tạo detail
            Dim rowCOLSPAN As New SourceGrid.Cells.Views.Cell
            rowCOLSPAN.BackColor = Color.FromArgb(192, 255, 192)
            Dim StyleLeft As New SourceGrid.Cells.Views.Cell
            StyleLeft.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleLeft
            Dim StyleRight As New SourceGrid.Cells.Views.Cell
            StyleRight.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleRight
            For i As Integer = 0 To dataSource.Rows.Count - 1
                dgvThongKeDoanhThu.Rows.Insert(i + 1)
                If dataSource.Rows(i)("TenKhachHang").ToString <> "" Then
                    dgvThongKeDoanhThu(i + 1, 0) = New SourceGrid.Cells.Cell(dataSource.Rows(i)("TenKhachHang").ToString)
                    dgvThongKeDoanhThu(i + 1, 0).ColumnSpan = 7
                    dgvThongKeDoanhThu(i + 1, 0).View = rowCOLSPAN
                Else
                    dgvThongKeDoanhThu(i + 1, 0) = New SourceGrid.Cells.Cell()
                    dgvThongKeDoanhThu(i + 1, 1) = New SourceGrid.Cells.Cell(dataSource.Rows(i)("MaSoThue").ToString)
                    dgvThongKeDoanhThu(i + 1, 1).View = StyleLeft
                    dgvThongKeDoanhThu(i + 1, 2) = New SourceGrid.Cells.Cell(dataSource.Rows(i)("DiaChi").ToString)
                    dgvThongKeDoanhThu(i + 1, 2).View = StyleLeft
                    dgvThongKeDoanhThu(i + 1, 3) = New SourceGrid.Cells.Cell(ConvertVietnamese(dataSource.Rows(i)("NgayBan").ToString))
                    dgvThongKeDoanhThu(i + 1, 3).View = StyleRight
                    dgvThongKeDoanhThu(i + 1, 4) = New SourceGrid.Cells.Cell(dataSource.Rows(i)("So").ToString)
                    dgvThongKeDoanhThu(i + 1, 4).View = StyleRight
                    dgvThongKeDoanhThu(i + 1, 5) = New SourceGrid.Cells.Cell(Format(Single.Parse(dataSource.Rows(i)("PhiVanChuyen")), "N02"))
                    dgvThongKeDoanhThu(i + 1, 5).View = StyleRight
                    dgvThongKeDoanhThu(i + 1, 6) = New SourceGrid.Cells.Cell(dataSource.Rows(i)("Loai").ToString)
                    dgvThongKeDoanhThu(i + 1, 6).View = StyleLeft
                End If
            Next
        Catch ex As Exception
            ShowError(ex)
        End Try
    End Sub
#End Region








    Private Sub cmdInThongKe_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Try
            Select Case ReportType
                Case 0
                    frmInThongKeDoanhThu.ReportType = 0
                    frmInThongKeDoanhThu.Ngay = dtpTheoNgay.Value.ToShortDateString
                    frmInThongKeDoanhThu.Show()
                Case 1
                    frmInThongKeDoanhThu.ReportType = 1
                    Dim Thang As MonthInfo
                    Thang = CType(cbxTheoThang.SelectedItem, MonthInfo)
                    frmInThongKeDoanhThu.Thang = Thang.TenThang
                    frmInThongKeDoanhThu.Show()
                Case 2
                    frmInThongKeDoanhThu.ReportType = 2
                    Dim Nam As YearInfo
                    Nam = CType(cbxTheoNam.SelectedItem, YearInfo)
                    frmInThongKeDoanhThu.Nam = Nam.TenNam
                    frmInThongKeDoanhThu.Show()
                Case 3
                    frmInThongKeDoanhThu.ReportType = 3
                    Dim TuNgay As Integer = ConvertDateToInt(dtpTuNgay.Value.ToString.Split(" ")(0))
                    Dim DenNgay As Integer = ConvertDateToInt(dtpDenNgay.Value.ToString.Split(" ")(0))
                    If DenNgay < TuNgay Then
                        Dim dlgRes As DialogResult
                        dlgRes = MessageBox.Show( _
                                  "Ngày đến không thể nhỏ hơn từ ngày.Vui lòng nhập lại", _
                                  "Thông báo", _
                            MessageBoxButtons.OK, _
                                  MessageBoxIcon.Question)
                        If dlgRes = Windows.Forms.DialogResult.OK Then
                            Exit Sub
                        End If
                    End If
                    frmInThongKeDoanhThu.TuNgay = TuNgay
                    frmInThongKeDoanhThu.DenNgay = DenNgay
                    frmInThongKeDoanhThu.sTuNgay = dtpTuNgay.Value.ToShortDateString
                    frmInThongKeDoanhThu.sDenNgay = dtpDenNgay.Value.ToShortDateString
                    frmInThongKeDoanhThu.Show()
            End Select
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
#Region "Sub Class"
    Private Class YearInfo
        Dim _Nam As Integer
        Dim _TenNam As String
        Public Sub New(ByVal Nam As Integer, ByVal TenNam As String)
            _Nam = Nam
            _TenNam = TenNam
        End Sub
        Public Function Nam() As Integer
            Return _Nam
        End Function
        Public Function TenNam() As String
            Return _TenNam
        End Function
        Public Overrides Function ToString() As String
            Return _TenNam
        End Function
    End Class
    Private Class MonthInfo
        Dim _Thang As Integer
        Dim _TenThang As String
        Public Sub New(ByVal Thang As Integer, ByVal TenThang As String)
            _Thang = Thang
            _TenThang = TenThang
        End Sub
        Public Function Thang() As Integer
            Return _Thang
        End Function
        Public Function TenThang() As String
            Return _TenThang
        End Function
        Public Overrides Function ToString() As String
            Return _TenThang
        End Function
    End Class
#End Region
#Region "Load data"
    Private Sub Nam_Load()
        Dim currentYear As Integer = Now.Year
        cbxTheoNam.Items.Clear()
        Try
            While (currentYear >= 2010)
                cbxTheoNam.Items.Add(New YearInfo(currentYear, currentYear.ToString))
                cbxNamThang.Items.Add(New YearInfo(currentYear, currentYear.ToString))
                currentYear = currentYear - 1
            End While
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
    Private IndexCurrentMonth As Integer = 0
    Private Sub Thang_Load()
        Dim currentYear As Integer = 1
        cbxTheoThang.Items.Clear()
        Try
            Dim i As Integer = 0
            While (currentYear <= 12)
                cbxTheoThang.Items.Add(New MonthInfo(currentYear, currentYear.ToString))
                If currentYear = Now.Month Then
                    IndexCurrentMonth = i
                End If
                currentYear = currentYear + 1
                i = i + 1
            End While
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
    'Sub KhachHang_Quick_Search()
    '    Try
    '        Dim dbConn As New OleDb.OleDbConnection
    '        Dim dataAccess As New DataAccess
    '        Dim dsKhachHang As New DataSet
    '        Dim dtKhachHang As New DataTable
    '        Dim query As String = "Select KhachHangId,MaKhachHang,TenKhachHang,SoDienThoai,DiaChi From DanhMucKhachHang Where TenKhachHang Like N'%" & txtTenKhachHang.Text.Trim & "%'"
    '        Dim adapter As OleDb.OleDbDataAdapter
    '        dataAccess.ConnectDatabase_DBAccess(dbConn)
    '        adapter = New OleDb.OleDbDataAdapter(query, dbConn)
    '        adapter.Fill(dsKhachHang)
    '        dbConn.Close()
    '        dtKhachHang = dsKhachHang.Tables(0)
    '        Panel_KhachHang_Search.Visible = True
    '        dgvKhachHang_Search.AutoGenerateColumns = False
    '        dgvKhachHang_Search.DataSource = dtKhachHang
    '    Catch ex As Exception
    '        MessageBox.Show(ex.ToString())
    '    End Try
    'End Sub
    Private Sub dgv_KhachHang_QuickSearch_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgv_KhachHang_QuickSearch.CellDoubleClick
        Try
            lblKhachHangId.Text = dgv_KhachHang_QuickSearch.Rows(e.RowIndex).Cells("KhachHangId_ThongKe").Value.ToString()
            txtTenKhachHang.Text = dgv_KhachHang_QuickSearch.Rows(e.RowIndex).Cells("TenKhachHang_S").Value.ToString()
            Panel_KhachHang.Visible = False
        Catch ex As Exception

        End Try
    End Sub
    Private IsEscape As Boolean = False
    'Private Sub txtTenKhachHang_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs)
    '    Try
    '        If e.KeyCode = Keys.Escape Then
    '            Panel_KhachHang_Search.Visible = False
    '            IsEscape = True
    '        End If
    '    Catch ex As Exception

    '    End Try
    'End Sub
    'Private Sub txtTenKhachHang_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs)
    '    Try
    '        If txtTenKhachHang.Text <> "" And IsEscape = False Then
    '            KhachHang_Quick_Search()
    '        Else
    '            Panel_KhachHang_Search.Visible = False
    '        End If
    '        IsEscape = False
    '    Catch ex As Exception
    '        MessageBox.Show(ex.ToString())
    '    End Try
    'End Sub
#End Region


    Private Sub cmdDong_KhachHangTab2Search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Try
            Panel_KhachHang.Visible = False
        Catch ex As Exception

        End Try
    End Sub
    Private Sub ExportExcel(ByVal Path As String, ByVal FileName As String)
        Try
            Dim SubTitle As String
            If rbtTheoNgay.Checked Then
                SubTitle = "Ngày " & formatDateVN2String(dtpTheoNgay.Value)
            ElseIf rbtTheoThang.Checked Then
                Dim Thang As MonthInfo
                Thang = CType(cbxTheoThang.SelectedItem, MonthInfo)
                Dim Nam As YearInfo
                Nam = CType(cbxNamThang.SelectedItem, YearInfo)
                SubTitle = " Tháng " & Thang.TenThang & " Năm " & Nam.TenNam
            ElseIf rbtTheoNam.Checked Then
                Dim Nam As YearInfo
                Nam = CType(cbxTheoNam.SelectedItem, YearInfo)
                SubTitle = " Năm " & Nam.TenNam
            ElseIf rbtTuNgay.Checked Then
                SubTitle = " Từ ngày " & formatDateVN2String(dtpTuNgay.Value) & " Đến ngày " & formatDateVN2String(dtpDenNgay.Value)
            ElseIf rbtTenKhachHang.Checked Then
                SubTitle = "Khách hàng " & txtTenKhachHang.Text.Trim()
            End If
            'Dim excelDoc As System.IO.StreamWriter = New System.IO.StreamWriter(FileName)
            Dim startExcelXML As String
            startExcelXML = "<?xml version=""1.0""?>" & vbCrLf & _
            "<?mso-application progid=""Excel.Sheet""?>" & vbCrLf & _
            "<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""" & vbCrLf & _
            "xmlns:o =""urn:schemas-microsoft-com:office:office""" & vbCrLf & _
            "xmlns:x =""urn:schemas-microsoft-com:office:excel""" & vbCrLf & _
            "xmlns:ss =""urn:schemas-microsoft-com:office:spreadsheet""" & vbCrLf & _
             "xmlns:html=""http://www.w3.org/TR/REC-html40""" & ">" & vbCrLf & _
             "<DocumentProperties xmlns=""urn:schemas-microsoft-com:office:office""" & ">" & vbCrLf & _
              "<LastAuthor>NG0C TRAM</LastAuthor>" & vbCrLf & _
              "<Created>2011-04-04T13:47:34Z</Created>" & vbCrLf & _
              "<Version>11.9999</Version>" & vbCrLf & _
             "</DocumentProperties>" & vbCrLf & _
             "<ExcelWorkbook xmlns=""urn:schemas-microsoft-com:office:excel""" & ">" & vbCrLf & _
              "<WindowHeight>10005</WindowHeight>" & vbCrLf & _
              "<WindowWidth>10005</WindowWidth>" & vbCrLf & _
              "<WindowTopX>120</WindowTopX>" & vbCrLf & _
              "<WindowTopY>135</WindowTopY>" & vbCrLf & _
              "<ProtectStructure>False</ProtectStructure>" & vbCrLf & _
              "<ProtectWindows>False</ProtectWindows>" & vbCrLf & _
             "</ExcelWorkbook>" & vbCrLf & _
             "<Styles>" & vbCrLf & _
              "<Style ss:ID=""Default""" & " ss:Name=""Normal""" & ">" & vbCrLf & _
               "<Alignment ss:Vertical=""Bottom""" & "/>" & vbCrLf & _
               "<Borders/>" & vbCrLf & _
               "<Font/>" & vbCrLf & _
               "<Interior/>" & vbCrLf & _
               "<NumberFormat/>" & vbCrLf & _
               "<Protection/>" & vbCrLf & _
              "</Style>" & vbCrLf & _
              "<Style ss:ID=""s22""" & ">" & vbCrLf & _
               "<Alignment ss:Horizontal=""Center""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
               "<Font x:Family=""Swiss""" & " ss:Size=""" & "16""" & " ss:Color=""" & "#FF0000""" & " ss:Bold=""" & "1""" & "/>" & vbCrLf & _
              "</Style>" & vbCrLf & _
              "<Style ss:ID=""" & "s24""" & ">" & vbCrLf & _
               "<Alignment ss:Horizontal=""Center""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
               "<Font x:Family=""Swiss""" & " ss:Color=""" & "#FF0000""" & " ss:Bold=""" & "1""" & "/>" & vbCrLf & _
              "</Style>" & vbCrLf & _
              "<Style ss:ID=""" & "s27""" & ">" & vbCrLf & _
               "<Font x:Family=""" & "Swiss""" & " ss:Bold=""" & "1""" & "/>" & vbCrLf & _
               "<Interior ss:Color=""" & "#99CCFF""" & " ss:Pattern=""" & "Solid""" & "/>" & vbCrLf & _
              "</Style>" & vbCrLf & _
              "<Style ss:ID=""" & "s29""" & ">" & vbCrLf & _
               "<Alignment ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
              "</Style>" & vbCrLf & _
                "<Style ss:ID=""" & "s30""" & ">" & vbCrLf & _
                "<Alignment ss:Horizontal=""" & "Left""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
                "<Font x:Family=""" & "Swiss""" & " ss:Bold=""" & "1""" & "/>" & vbCrLf & _
                "<Interior ss:Color=""" & "#CCFFCC""" & " ss:Pattern=""" & "Solid""" & "/>" & vbCrLf & _
                "</Style>" & vbCrLf & _
              "<Style ss:ID=""" & "s31""" & ">" & vbCrLf & _
               "<Alignment ss:Horizontal=""" & "Left""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
               "</Style>" & vbCrLf & _
              "<Style ss:ID=""" & "s33""" & ">" & vbCrLf & _
               "<Alignment ss:Horizontal=""" & "Right""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
               "</Style>" & vbCrLf & _
              "<Style ss:ID=""" & "s32""" & ">" & vbCrLf & _
              "<Alignment ss:Horizontal=""" & "Center""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
              "</Style>" & vbCrLf & _
             "</Styles>" & vbCrLf & _
             "<Worksheet ss:Name=""" & "Sheet1""" & ">" & vbCrLf & _
              "<Table>" & vbCrLf & _
               "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "98.25""" & "/>" & vbCrLf & _
               "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "99.75""" & "/>" & vbCrLf & _
               "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "149.25""" & "/>" & vbCrLf & _
               "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "101.25""" & "/>" & vbCrLf & _
               "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "106.5""" & "/>" & vbCrLf & _
               "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "93.75""" & "/>" & vbCrLf & _
               "<Column ss:Width=""" & "72""" & "/>" & vbCrLf & _
               "<Column ss:Width=""" & "93.75""" & "/>" & vbCrLf & _
               "<Column ss:Width=""" & "50.25""" & "/>" & vbCrLf & _
               "<Column ss:Width=""" & "69""" & "/>" & vbCrLf & _
               "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "84.75""" & "/>" & vbCrLf & _
               "<Row ss:AutoFitHeight=""" & "0""" & " ss:Height=""" & "24""" & ">" & vbCrLf & _
                "<Cell ss:MergeAcross=""" & "5""" & " ss:StyleID=""" & "s22""" & "><Data ss:Type=""" & "String""" & ">BÁO CÁO THỐNG KÊ CHI PHÍ VẬN CHUYỂN</Data></Cell>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
               "</Row>" & vbCrLf & _
               "<Row ss:Height=""" & "20.25""" & ">" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:MergeAcross=""" & "1""" & " ss:StyleID=""" & "s24""" & "><Data ss:Type=""" & "String""" & ">" & SubTitle & "</Data></Cell>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
               "</Row>" & vbCrLf & _
               "<Row>" & vbCrLf & _
                "<Cell ss:Index=""" & "4""" & " ss:StyleID=""" & "s33""" & "/>" & vbCrLf & _
                "<Cell ss:Index=""" & "9""" & " ss:StyleID=""" & "s33""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s33""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s33""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s33""" & "/>" & vbCrLf & _
               "</Row>" & vbCrLf & _
               "<Row ss:Index=""" & "6""" & ">" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">TÊN KHÁCH HÀNG</Data></Cell>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">MÃ SỐ THUẾ</Data></Cell>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">ĐỊA CHỈ</Data></Cell>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">NGÀY BÁN</Data></Cell>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">SỐ HÓA ĐƠN</Data></Cell>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">PHÍ VẬN CHUYỂN</Data></Cell>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">HÌNH THỨC</Data></Cell>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
               "</Row>"
            'write
            ExportToFile(Path, FileName, startExcelXML)
            Dim contentExcelXML As String = ""
            'contentExcelXML = DevideRowGrid()
            Dim EndRow As Integer = dgvThongKeDoanhThu.RowsCount - 1
            Dim StartRow As Integer = 0
            Dim K As Integer = EndRow \ 100
            Dim SoDu As Integer = EndRow Mod 100
            Dim aK(100) As Integer
            For i As Integer = 1 To 100
                If K * i <= EndRow Then
                    aK(i) = K * i
                End If
            Next
            aK(100) = 100 * K + SoDu
            Dim sRet As String = ""
            frmBusyLoading.Show()
            frmBusyLoading.progress.Minimum = 0
            frmBusyLoading.progress.Maximum = 100
            For i As Integer = 0 To 99
                sRet = ConvertExcel(aK(i) + 1, aK(i + 1))
                If sRet <> "" Then
                    ExportToFile(Path, FileName, sRet)
                End If
                frmBusyLoading.progress.Value = i
            Next
            frmBusyLoading.Close()
            'Dim contentExcelXML As String = ""
            'For i As Integer = 1 To dgvThongKeDoanhThu.RowsCount - 1
            '    If dgvThongKeDoanhThu(i, 0).ColumnSpan = 6 Then
            '        contentExcelXML = contentExcelXML & "<Row>" & vbCrLf & _
            '                                "<Cell ss:MergeAcross=""" & "5""" & " ss:StyleID=""" & "s30""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 0).Value & "</Data></Cell>" & vbCrLf & _
            '                            "</Row>"
            '    Else
            '        contentExcelXML = contentExcelXML & "<Row>" & vbCrLf & _
            '    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & "></Data></Cell>" & vbCrLf & _
            '    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 1).Value & "</Data></Cell>" & vbCrLf & _
            '    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 2).Value & "</Data></Cell>" & vbCrLf & _
            '    "<Cell ss:StyleID=""" & "s32""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 3).Value & "</Data></Cell>" & vbCrLf & _
            '    "<Cell ss:StyleID=""" & "s32""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 4).Value & "</Data></Cell>" & vbCrLf & _
            '    "<Cell ss:StyleID=""" & "s33""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 5).Value & "</Data></Cell>" & vbCrLf & _
            '    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
            '    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
            '    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
            '    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
            '    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
            '   "</Row>"
            '    End If

            'Next
            Dim endExcelXML As String
            endExcelXML = "</Table>" & vbCrLf & _
              "<WorksheetOptions xmlns=""" & "urn:schemas-microsoft-com:office:excel""" & ">" & vbCrLf & _
               "<Print>" & vbCrLf & _
                "<ValidPrinterInfo/>" & vbCrLf & _
                "<PaperSizeIndex>0</PaperSizeIndex>" & vbCrLf & _
                "<HorizontalResolution>600</HorizontalResolution>" & vbCrLf & _
                "<VerticalResolution>600</VerticalResolution>" & vbCrLf & _
               "</Print>" & vbCrLf & _
               "<Selected/>" & vbCrLf & _
               "<Panes>" & vbCrLf & _
                "<Pane>" & vbCrLf & _
                 "<Number>3</Number>" & vbCrLf & _
                 "<ActiveRow>1</ActiveRow>" & vbCrLf & _
                 "<ActiveCol>2</ActiveCol>" & vbCrLf & _
                 "<RangeSelection>R2C3:R2C4</RangeSelection>" & vbCrLf & _
                "</Pane>" & vbCrLf & _
               "</Panes>" & vbCrLf & _
               "<ProtectObjects>False</ProtectObjects>" & vbCrLf & _
               "<ProtectScenarios>False</ProtectScenarios>" & vbCrLf & _
              "</WorksheetOptions>" & vbCrLf & _
             "</Worksheet>" & vbCrLf & _
            "</Workbook>"
            ExportToFile(Path, FileName, endExcelXML)
            'excelDoc.Write(startExcelXML & contentExcelXML & endExcelXML)
            'excelDoc.Close()
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Sub
    Private Function ConvertExcel(ByVal iBegin As Integer, ByVal iEnd As Integer) As String
        Try
            Dim contentExcelXML As String = ""
            For i As Integer = iBegin To iEnd
                If dgvThongKeDoanhThu(i, 0).ColumnSpan = 7 Then
                    contentExcelXML = contentExcelXML & "<Row>" & vbCrLf & _
                                            "<Cell ss:MergeAcross=""" & "6""" & " ss:StyleID=""" & "s30""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 0).Value & "</Data></Cell>" & vbCrLf & _
                                        "</Row>"
                Else
                    contentExcelXML = contentExcelXML & "<Row>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & "></Data></Cell>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 1).Value & "</Data></Cell>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 2).Value & "</Data></Cell>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s32""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 3).Value & "</Data></Cell>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s32""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 4).Value & "</Data></Cell>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s33""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 5).Value & "</Data></Cell>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 6).Value & "</Data></Cell>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
               "</Row>"
                End If

            Next
            Return contentExcelXML
        Catch ex As Exception

        End Try
    End Function
    Private Sub cemXuatExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cemXuatExcel.Click
        Try
            frmSaveFile.FileType = ".xls"
            ShowModalForm(frmSaveFile, False, False)
            Dim result As Boolean
            result = frmSaveFile.Result
            If result Then
                Dim Path As String = frmSaveFile.Path
                Dim FileName As String = frmSaveFile.FileName
                'ExportExcel(Path & "/" & FileName & ".xls")
                ExportExcel(Path, FileName & ".xls")
            End If
            frmSaveFile.txtPath.Text = ""
            frmSaveFile.txtFile.Text = ""
        Catch ex As Exception

        End Try
    End Sub
    Private Sub txtTenKhachHang_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtTenKhachHang.KeyDown
        Try
            If e.KeyCode = Keys.Enter Then
                lblKhachHangId.Text = dgv_KhachHang_QuickSearch.Rows(dgv_KhachHang_QuickSearch.CurrentCell.RowIndex).Cells("KhachHangId_Search").Value.ToString()
                '---------Lấy Thống Tin Khách Hàng----------
                Dim dbConn As New OleDb.OleDbConnection
                Dim dataAccess As New DataAccess
                Dim dsTinhThanh As New DataSet
                Dim dtTinhThanh As New DataTable
                Dim query As String = "Select CodeKhachHang,TenKhachHang From DanhMucKhachHang Where KhachHangId=" & lblKhachHangId.Text
                Dim adapter As OleDb.OleDbDataAdapter
                dataAccess.ConnectDatabase_DBAccess(dbConn)
                adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                adapter.Fill(dsTinhThanh)

                dtTinhThanh = dsTinhThanh.Tables(0)
                '
                txtMaKhachHang.Text = IIf(IsDBNull(dtTinhThanh.Rows(0)("CodeKhachHang")), "", dtTinhThanh.Rows(0)("CodeKhachHang"))
                txtTenKhachHang.Text = IIf(IsDBNull(dtTinhThanh.Rows(0)("TenKhachHang")), "", dtTinhThanh.Rows(0)("TenKhachHang"))
                Panel_KhachHang.Visible = False
                dbConn.Close()
            ElseIf e.KeyCode = Keys.Up Or e.KeyCode = Keys.Down Then

            End If
        Catch ex As Exception

        End Try
    End Sub
    Private Sub txtTenKhachHang_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtTenKhachHang.KeyUp
        Try
            If txtTenKhachHang.Text <> "" Then
                KhachHang_Quick_Search()
                If e.KeyCode = Keys.Up Or e.KeyCode = Keys.Down Then
                    dgv_KhachHang_QuickSearch.Focus()
                    Panel_KhachHang.Visible = True
                End If
            Else
                Panel_KhachHang.Visible = False
                lblKhachHangId.Text = "0"
                txtMaKhachHang.Text = ""
            End If
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Sub
    Private Sub txtTenKhachHang_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtTenKhachHang.Leave
        Try
            Panel_KhachHang.Visible = False
        Catch ex As Exception

        End Try
    End Sub
    Sub KhachHang_Quick_Search()
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dsKhachHang As New DataSet
            Dim dtKhachHang As New DataTable
            Dim query As String = "Select KhachHangId,MaKhachHang,TenKhachHang,SoDienThoai,DiaChi From DanhMucKhachHang Where TenKhachHang Like N'%" & txtTenKhachHang.Text.Trim & "%'"
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dsKhachHang)
            dbConn.Close()
            dtKhachHang = dsKhachHang.Tables(0)
            Panel_KhachHang.Visible = True
            dgv_KhachHang_QuickSearch.AutoGenerateColumns = False
            dgv_KhachHang_QuickSearch.DataSource = dtKhachHang
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Sub
    Public Sub AutoComplete_MaKhachHang()
        Try
            'Auto Complete 
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dtCustomerInfo As New DataTable
            Dim query As String = "Select CodeKhachHang From DanhMucKhachHang Where Xoa=0"
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtCustomerInfo)
            dbConn.Close()
            aMaKhachHang = New AutoCompleteStringCollection
            For i As Integer = 0 To dtCustomerInfo.Rows.Count - 1
                aMaKhachHang.Add(dtCustomerInfo.Rows(i)("CodeKhachHang"))
            Next
            txtMaKhachHang.AutoCompleteMode = AutoCompleteMode.SuggestAppend
            txtMaKhachHang.AutoCompleteSource = AutoCompleteSource.CustomSource
            txtMaKhachHang.AutoCompleteCustomSource = aMaKhachHang
        Catch ex As Exception

        End Try
    End Sub
    Private Sub txtMaKhachHang_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtMaKhachHang.KeyDown
        Try
            If e.KeyCode = Keys.Enter Then
                'Truyen MaKhachHang De Lay Thong Tin
                Dim dtKhachHangInfo As New DataTable
                Dim dbConn As New OleDb.OleDbConnection
                Dim dataAccess As New DataAccess
                Dim adapter As OleDb.OleDbDataAdapter
                Dim dbCommand As OleDb.OleDbCommand
                dataAccess.ConnectDatabase_DBAccess(dbConn)
                dbCommand = New OleDbCommand("spKhachHang_Item", dbConn)
                dbCommand.CommandType = CommandType.StoredProcedure
                dbCommand.Parameters.Add("@KhachHangId", OleDbType.Integer).Value = 0
                dbCommand.Parameters.Add("@CodeKhachHang", OleDbType.VarWChar).Value = txtMaKhachHang.Text.Trim
                dbCommand.Parameters.Add("@Type", OleDbType.Integer).Value = 1
                adapter = New OleDb.OleDbDataAdapter(dbCommand)
                adapter.Fill(dtKhachHangInfo)
                dbConn.Close()
                If dtKhachHangInfo.Rows.Count > 0 Then
                    lblKhachHangId.Text = dtKhachHangInfo.Rows(0)("KhachHangId")
                    txtMaKhachHang.Text = IIf(IsDBNull(dtKhachHangInfo.Rows(0)("CodeKhachHang")), "", dtKhachHangInfo.Rows(0)("CodeKhachHang"))
                    txtTenKhachHang.Text = IIf(IsDBNull(dtKhachHangInfo.Rows(0)("TenKhachHang")), "", dtKhachHangInfo.Rows(0)("TenKhachHang"))
                Else
                    txtMaKhachHang.Focus()
                End If
            End If
        Catch ex As Exception

        End Try
    End Sub
    Private Sub dgv_KhachHang_QuickSearch_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles dgv_KhachHang_QuickSearch.KeyDown
        Try
            Select Case e.KeyCode
                Case Keys.Down
                    Panel_KhachHang.Visible = True
                Case Keys.Up
                    Panel_KhachHang.Visible = True
                Case Keys.Enter
                    lblKhachHangId.Text = dgv_KhachHang_QuickSearch.Rows(dgv_KhachHang_QuickSearch.CurrentCell.RowIndex).Cells("KhachHangId_Search").Value.ToString()
                    '---------Lấy Thống Tin Khách Hàng----------
                    Dim dbConn As New OleDb.OleDbConnection
                    Dim dataAccess As New DataAccess
                    Dim dsTinhThanh As New DataSet
                    Dim dtTinhThanh As New DataTable
                    Dim query As String = "Select CodeKhachHang,TenKhachHang From DanhMucKhachHang Where KhachHangId=" & lblKhachHangId.Text
                    Dim adapter As OleDb.OleDbDataAdapter
                    dataAccess.ConnectDatabase_DBAccess(dbConn)
                    adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                    adapter.Fill(dsTinhThanh)

                    dtTinhThanh = dsTinhThanh.Tables(0)
                    '
                    txtMaKhachHang.Text = IIf(IsDBNull(dtTinhThanh.Rows(0)("CodeKhachHang")), "", dtTinhThanh.Rows(0)("CodeKhachHang"))
                    txtTenKhachHang.Text = IIf(IsDBNull(dtTinhThanh.Rows(0)("TenKhachHang")), "", dtTinhThanh.Rows(0)("TenKhachHang"))
                    '
                    Panel_KhachHang.Visible = False
                    dbConn.Close()
                Case Else
                    txtTenKhachHang.Focus()
            End Select
        Catch ex As Exception

        End Try
    End Sub

End Class